const express = require('express');
const mysql2 = require('mysql2');


// 创建到数据库的连接
const connection = mysql2.createConnection({
    // 用于指定数据库服务器
    host: 'localhost',
    // 指定MySQL用户名
    user: 'root',
    // 指定数据库名
    database: 'ecut'
});

const router = express.Router();

// GET `/student/list`
router.get('/student/list', (req,resp)=>{
    const sql = `SELECT id , name , gender , birthdate FROM t_students ORDER BY id ASC` ;
    const handler = (error,results)=>{
        if( error ) {
            resp.send( '<p>查询失败</p>');
            return;
        }
        
        if( results.length != 0 ) {
            let listPage = `<div class="student-list">`;
            results.forEach( stu => {
                listPage += `<div class="student">
                                <span>${stu.id}</span>
                                <span>${stu.name}</span>
                                <span>${stu.gender}</span>
                                <span>${stu.birthdate.toLocaleDateString()}</span>
                                <span>
                                    <a href="/student/edit/${stu.id}">编辑</a>
                                    <a href="/student/remove/${stu.id}">删除</a>
                                </span>
                            </div>`;
            });
            listPage += `</div>`
            listPage += `<a href="/index.html">添加</a>`;
            resp.send( listPage );
            return;
        }

        resp.send( `<p>暂无数据。要不先<a href="/index.html">添加</a>一个？</p>` );
    }
    // 通过 connection 实现查询操作
    connection.execute( sql , handler );
});

// POST `/student/add`
router.post('/student/add',(req,resp)=>{
    // 解构赋值
    let { name , gender , birthdate } = req.body ;
    let sql = 'INSERT INTO t_students ( name , gender , birthdate ) VALUES ( ? , ? , ? )';
    let params = [ name , gender , birthdate ];
    let handler = (error,results) => {
        if(error){
            resp.send(`<p>保存失败: ${ error.message }</p>`);
            return ;
        }
        // resp.send(`<p>保存成功。<a href="/student/list">返回列表</a></p>`);
        resp.redirect('/student/list'); // 重定向到学生列表
    }
    connection.execute( sql , params , handler );
});

// GET `/student/remove/:id`
router.get('/student/remove/:id',(req,resp)=>{
    // let id = req.params.id 
    let { id } = req.params ;
    let sql = `DELETE FROM t_students WHERE id = ?` ;
    let params = [ id ] ;
    let handler = (error,results) => {
        if( error ) {
            resp.send(`<p>删除失败: ${ error.message }</p>`);
            return ;
        }
        // resp.send('<p>删除成功。<a href="/student/list">返回列表</a></p>');
        resp.redirect('/student/list'); // 重定向到学生列表
    }
    connection.execute( sql , params , handler );
});

// GET `/student/edit/:id`
router.get('/student/edit/:id',(req,resp)=>{
    let editPage = '' ;

    // 任务:
    // 根据 数组版 CRUD 操作 拼接 编辑页面

    resp.send( editPage );
});

// POST `/student/edit`
router.post('/student/edit',(req,resp)=>{
    let { id , name , gender , birthdate } = req.body;
    let sql = 'UPDATE t_students SET name = ? , gender = ? , birthdate = ? WHERE id = ? ';
    let params = [ name , gender , birthdate , id ];

    // 任务: 执行更新操作并向客户端返回执行结果

    resp.end(); // 这行代码是为了响应结束，不让浏览器等待
});

module.exports = router ;